set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
/* *******************************************************************
** DATE :
** Writer :
** Memo :
******************************************************************** */
CREATE PROCEDURE [dbo].[TSP_Recruit_GetList]
(
	@IsTransaction	tinyint			= 0
,	@ErrorCode		int				= 0		OUTPUT
,	@ErrorMessage	nvarchar( 1000 )= ''	OUTPUT

,	@n1Status	tinyint	= NULL
,	@n1JobTime	tinyint = NULL

,	@PageNo	int	= 1	
,	@PageSize	tinyint	= 10	
,	@CntRow	int	= NULL	OUTPUT
,	@CntTotal	int	= NULL	OUTPUT
)
as
Set NOCOUNT ON

-- Frk Default Value
DECLARE @FrkDateNow DateTime
DECLARE @FrkRowCount int
DECLARE @FrkTranCount int
DECLARE @FrkErrorCode int
DECLARE @FrkErrorMessage nvarchar( 1000 )
Set @FrkDateNow = GetDate()
set @FrkTranCount = @@TRANCOUNT
-- Frk Check


If ( @IsTransaction = 0 )
Begin
	If ( @FrkTranCount < 1 )
	Begin
		Set @FrkErrorCode = -1
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Else If ( @FrkTranCount > 1 )
	Begin
		Set @FrkErrorCode = -2
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
End
Else
Begin
	If ( @FrkTranCount <> 0 )
	Begin
		Set @FrkErrorCode = -3
		Set @FrkErrorMessage = 'Transaction.'
		goto ERROR
	End
	Begin Tran
End

-- Implementation

DECLARE @SkipPage INT
SET @SkipPage = ( ( @PageNo - 1 ) * @PageSize )

IF ( @n1Status IS NULL AND @n1JobTime IS NULL )
BEGIN
	SELECT	
		@CntTotal = COUNT( * )
	FROM
		dbo.TB_Recruit with ( nolock )

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			n4RecruitNo
		,	strName
		,	strPersonalNo
		,	strPhone
		,	strEmail
		,	strPostNo
		,	strAddress
		,	strHopeStore
		,	n1JobTime
		,	strAcademic
		,	strCareer
		,	strLicense
		,	strFileURL
		,	n1Status
		,	dtCreateDate
		,	dtModifyDate
		,	strManagerName
		,	strDescription
		FROM
			dbo.TB_Recruit with ( nolock )
		ORDER BY
			n4RecruitNo DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			n4RecruitNo
		,	strName
		,	strPersonalNo
		,	strPhone
		,	strEmail
		,	strPostNo
		,	strAddress
		,	strHopeStore
		,	n1JobTime
		,	strAcademic
		,	strCareer
		,	strLicense
		,	strFileURL
		,	n1Status
		,	dtCreateDate
		,	dtModifyDate
		,	strManagerName
		,	strDescription
		FROM
		(
			SELECT
				n4RecruitNo
			,	strName
			,	strPersonalNo
			,	strPhone
			,	strEmail
			,	strPostNo
			,	strAddress
			,	strHopeStore
			,	n1JobTime
			,	strAcademic
			,	strCareer
			,	strLicense
			,	strFileURL
			,	n1Status
			,	dtCreateDate
			,	dtModifyDate
			,	strManagerName
			,	strDescription
			,	ROW_NUMBER() OVER ( ORDER BY
				n4RecruitNo DESC
			) AS ROWNUM
			FROM
				dbo.TB_Recruit with ( nolock )
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		
	END
END
ELSE IF ( @n1Status IS NOT NULL )
BEGIN
	SELECT	
		@CntTotal = COUNT( * )
	FROM
		dbo.TB_Recruit with ( nolock )
	WHERE
		n1Status = @n1Status

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			n4RecruitNo
		,	strName
		,	strPersonalNo
		,	strPhone
		,	strEmail
		,	strPostNo
		,	strAddress
		,	strHopeStore
		,	n1JobTime
		,	strAcademic
		,	strCareer
		,	strLicense
		,	strFileURL
		,	n1Status
		,	dtCreateDate
		,	dtModifyDate
		,	strManagerName
		,	strDescription
		FROM
			dbo.TB_Recruit with ( nolock )
		WHERE
			n1Status = @n1Status
		AND	1 = CASE WHEN @n1JobTime IS NULL OR n1JobTime = @n1JobTime THEN 1 ELSE 0 END
		ORDER BY
			n1Status DESC, n4RecruitNo DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			n4RecruitNo
		,	strName
		,	strPersonalNo
		,	strPhone
		,	strEmail
		,	strPostNo
		,	strAddress
		,	strHopeStore
		,	n1JobTime
		,	strAcademic
		,	strCareer
		,	strLicense
		,	strFileURL
		,	n1Status
		,	dtCreateDate
		,	dtModifyDate
		,	strManagerName
		,	strDescription
		FROM
		(
			SELECT
				n4RecruitNo
			,	strName
			,	strPersonalNo
			,	strPhone
			,	strEmail
			,	strPostNo
			,	strAddress
			,	strHopeStore
			,	n1JobTime
			,	strAcademic
			,	strCareer
			,	strLicense
			,	strFileURL
			,	n1Status
			,	dtCreateDate
			,	dtModifyDate
			,	strManagerName
			,	strDescription
			,	ROW_NUMBER() OVER ( ORDER BY
				n1Status DESC, n4RecruitNo DESC
			) AS ROWNUM
			FROM
				dbo.TB_Recruit with ( nolock )
			WHERE
				n1Status = @n1Status
			AND	1 = CASE WHEN @n1JobTime IS NULL OR n1JobTime = @n1JobTime THEN 1 ELSE 0 END
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			n1Status DESC, n4RecruitNo DESC
	END
END
ELSE IF ( @n1JobTime IS NOT NULL )
BEGIN
	SELECT	
		@CntTotal = COUNT( * )
	FROM
		dbo.TB_Recruit with ( nolock )
	WHERE
		n1Status = @n1Status

	IF ( @PageNo = 1 )
	BEGIN
		SELECT	Top( @PageSize )
			n4RecruitNo
		,	strName
		,	strPersonalNo
		,	strPhone
		,	strEmail
		,	strPostNo
		,	strAddress
		,	strHopeStore
		,	n1JobTime
		,	strAcademic
		,	strCareer
		,	strLicense
		,	strFileURL
		,	n1Status
		,	dtCreateDate
		,	dtModifyDate
		,	strManagerName
		,	strDescription
		FROM
			dbo.TB_Recruit with ( nolock )
		WHERE
			n1JobTime = @n1JobTime
		ORDER BY
			n1JobTime DESC, n4RecruitNo DESC
	END
	ELSE
	BEGIN
		SELECT	Top( @PageSize )
			n4RecruitNo
		,	strName
		,	strPersonalNo
		,	strPhone
		,	strEmail
		,	strPostNo
		,	strAddress
		,	strHopeStore
		,	n1JobTime
		,	strAcademic
		,	strCareer
		,	strLicense
		,	strFileURL
		,	n1Status
		,	dtCreateDate
		,	dtModifyDate
		,	strManagerName
		,	strDescription
		FROM
		(
			SELECT
				n4RecruitNo
			,	strName
			,	strPersonalNo
			,	strPhone
			,	strEmail
			,	strPostNo
			,	strAddress
			,	strHopeStore
			,	n1JobTime
			,	strAcademic
			,	strCareer
			,	strLicense
			,	strFileURL
			,	n1Status
			,	dtCreateDate
			,	dtModifyDate
			,	strManagerName
			,	strDescription
			,	ROW_NUMBER() OVER ( ORDER BY
				n1JobTime DESC, n4RecruitNo DESC
			) AS ROWNUM
			FROM
				dbo.TB_Recruit with ( nolock )
			WHERE
				n1JobTime = @n1JobTime
		)	A
		WHERE
			ROWNUM > @SkipPage
		AND	ROWNUM <= @SkipPage + @PageSize
		ORDER BY
			n1JobTime DESC, n4RecruitNo DESC
	END
END


-- Frk Error Check
SELECT
	@FrkErrorCode = @@ERROR
,	@FrkRowCount = @@RowCount

-- End

COMPLETE:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Commit Tran
		End
	End
	SET @ErrorCode = 0
	SET @ErrorMessage = ''
	return 0
ERROR:
	If ( @IsTransaction <> 0 )
	Begin
		If ( @@TRANCOUNT = 1 )
		Begin
			Rollback Tran
		End
	End
	SET @ErrorCode = @FrkErrorCode
	SET @ErrorMessage = @FrkErrorMessage
	return @FrkErrorCode
